﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Shop.DAL
{
    public class BaseService<T> where T : class,new()
    {
        public virtual int Add(T entity)
        {
            var tablename = entity.GetType().Name;  // 表名
            var props = entity.GetType().GetProperties().Where(p=>p.Name!="Id"); // 排除主键Id
            var propNames = props.Select(p => p.Name);  // 获取所有属性
            var pramNames = props.Select(p => "@" + p.Name);
            var fields = string.Join(",", propNames);   // 拼接属性
            var param = string.Join(",", pramNames);
            string sql = $"insert into {tablename}({fields}) values({param})";

            var ps = new List<SqlParameter>();
            foreach (var p in props)
            {
                var obj = new SqlParameter();
                obj.ParameterName = p.Name;
                obj.Value = p.GetValue(entity);
                ps.Add(obj);
            }
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
            }
            catch
            {
                throw;
            }
        
            
        }

        public virtual int Update(T entity)
        {
            var tablename = entity.GetType().Name;  // 表名
            var props = entity.GetType().GetProperties();
            var propNames = props.Select(p => p.Name);  // 获取所有属性
            var pramNames = props.Select(p => "@" + p.Name);
            var fields = string.Join(",", propNames);   // 拼接属性
            var param = string.Join(",", pramNames);

            var kv = props.Where(p=>p.Name!="Id").Select(p => p.Name + "=@" + p.Name.ToLower());
            var kvString=string.Join(",", kv);


            string sql = $"update {tablename} set {kvString} where id=@id";

            var ps = new List<SqlParameter>();
            foreach (var p in props)
            {
                var obj = new SqlParameter();
                obj.ParameterName = p.Name.ToLower();
                obj.Value = p.GetValue(entity);
                ps.Add(obj);
            }

            try
            {
                return SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
            }
            catch
            {
                throw;
            }
        }
        public virtual int Delete(int id)
        {
            var table = typeof(T).Name;
            string sql = $"delete from {table} where id=@id";
            SqlParameter[] sp = { new SqlParameter("@id", id) };
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, sp);
            }
            catch
            {
                throw;
            }
        }

        public virtual T GetEntity(int id)
        {
            var table = typeof(T).Name;
            string sql = $"select * from {table} where id=@id";
            SqlParameter[] sp = { new SqlParameter("@id", id) };
            try
            {
                using (var reader = SqlHelper.ExecuteReader(sql, sp))
                {

                    if (reader.Read())
                    {
                        var t = new T();
                        foreach (var prop in typeof(T).GetProperties())
                        {
                            prop.SetValue(t, reader[prop.Name]);
                        }
                        return t;
                    }
                    return null;
                }
            }
            catch
            {
                throw;
            }
        }


        public virtual List<T> GetList(string where, SqlParameter[] sp)
        {
            var table = typeof(T).Name;
            var props = typeof(T).GetProperties(); // 所有属性
            string sql = $"select * from {table} where 1=1";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " and " + where;
            }
            try
            {
                using (var reader = SqlHelper.ExecuteReader(sql, sp))
                {
                    List<T> list = new List<T>();
                    while (reader.Read())
                    {
                        var t = new T();
                        foreach (var prop in props)
                        {
                            prop.SetValue(t, reader[prop.Name]);
                        }
                        list.Add(t);

                    }
                    return list;
                }
            }
            catch
            {
                throw;
            }

        }
        public virtual List<T> GetList(string where, SqlParameter[] sp, int pageindex, int pageSize, out int total)
        {
            var table = typeof(T).Name;
            var props = typeof(T).GetProperties(); // 所有属性

            where = string.IsNullOrEmpty(where) ? " and 1=1" : " and " + where;
            string sql = $"select count(*) from {table} where 1=1{where}";
            total = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, sp));

            sql = $"select top {pageSize} * from {table} where id not in (select top {pageSize * (pageindex - 1)} id from {table} where 1=1 {where} order by id desc) {where} order by id desc";
            try
            {
                using (var reader = SqlHelper.ExecuteReader(sql, sp))
                {
                    List<T> list = new List<T>();
                    while (reader.Read())
                    {
                        var t = new T();
                        foreach (var prop in props)
                        {
                            prop.SetValue(t, reader[prop.Name]);
                        }
                        list.Add(t);

                    }
                    return list;
                }
            }
            catch
            {
                throw;
            }
        }

    }
}
